package com.elric.mall.common.utils;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.elric.mall.common.annotation.ImportExcelAnnotation;
import com.elric.mall.common.domain.ExcelCols;
import com.elric.mall.common.domain.ExcelRows;
import com.elric.mall.common.domain.ImportBase;
import com.elric.mall.common.exception.ApiException;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.MultiValueMap;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 实现功能：excel导入工具类
 *
 * @author 作者[Yuxuan.he]
 * @version 1.0.00  2021-09-16 10:44
 */
public class ImportExcelUtil {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static final String POINT = ".";

    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";

    public static <T extends ImportBase> List<T> readExcelByRequest (MultipartHttpServletRequest request, T clazz) {
        MultiValueMap<String, MultipartFile> multiFileMap = request.getMultiFileMap();
        Collection<List<MultipartFile>> multipartFiles = multiFileMap.values();
        List<T> excelEntities = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(multipartFiles)) {
            try {
                for (List<MultipartFile> multipartFile : multipartFiles) {
                    for (int i = 0; i < multipartFile.size(); i++) {
                        MultipartFile file = multipartFile.get(i);
                        if (file != null && !file.isEmpty()) {
                            String fileName = file.getOriginalFilename();
                            String strs[] = fileName.split("[.]");
                            String suffix = strs[strs.length - 1];
                            if (!ImportExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(suffix) && !ImportExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(suffix)) {
                                throw new ApiException("请上传xls、xlsx格式的文件");
                            }
                            //读取excel数据
                            excelEntities =ImportExcelUtil.readExcelForClazz(file.getInputStream(),true,fileName, clazz);
                        }
                    }
                }
            } catch (Exception e) {
                throw new ApiException(e.getMessage());
            }
        }
        return excelEntities;
    }


    /**
     * 读取Excel文件
     *
     * @param path          文件路径
     * @param iscontalTitle 是否包含标题
     * @return
     * @throws IOException
     */
    public static Map<String, List<ExcelRows>> readExcel(String path, boolean iscontalTitle) throws IOException {
        if (StringUtils.isEmpty(path)) {
            return null;
        } else {
            String postfix = getPostfix(path);
            if (!"".equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXlsForPath(path, iscontalTitle);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsxForPath(path, iscontalTitle);
                }
            } else {
                System.out.println(path + NOT_EXCEL_FILE);
            }
        }
        return null;
    }

    public static <T extends ImportBase> List<T> readExcelForClazz(String path, boolean iscontalTitle, T clazz) throws Exception {
        // 存储excel数据
        List<T> list = new ArrayList<>();
        if (StringUtils.isEmpty(path)) {
            return list;
        } else {
            String postfix = getPostfix(path);
            if (!"".equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    list= readXlsForClazz(path, iscontalTitle,clazz);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    list= readXlsxForClazz(path, iscontalTitle,clazz);
                }
            } else {
                System.out.println(path + NOT_EXCEL_FILE);
            }
        }
        return list;
    }

    /**
     * 通过文件流读取
     *
     * @param inputStream
     * @param iscontalTitle
     * @param fileName
     * @param object
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T extends ImportBase> List<T> readExcelForClazz(InputStream inputStream, boolean iscontalTitle, String fileName, T object) throws Exception {
        // 存储excel数据
        List<T> list = new ArrayList<>();
        if (inputStream==null) {
            return list;
        } else {
            String postfix = getPostfix(fileName);
            if (!"".equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    list= readXlsClazz(inputStream, iscontalTitle,object);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    list= readXlsxClazz(inputStream, iscontalTitle,object);
                }
            } else {
                throw new ApiException(NOT_EXCEL_FILE);
            }
        }
        return list;
    }

    /**
     * 读取Excel文件
     *
     * @param inputStream          文件流
     * @param iscontalTitle 是否包含标题
     * @return
     * @throws IOException
     */
    public static Map<String, List<ExcelRows>> readExcel(InputStream inputStream, boolean iscontalTitle,String fileName) throws IOException {
        if (inputStream==null) {
            return null;
        } else {
            String postfix = getPostfix(fileName);
            if (!"".equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(inputStream, iscontalTitle);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(inputStream, iscontalTitle);
                }
            } else {
                throw new ApiException(NOT_EXCEL_FILE);
            }
        }
        return null;
    }

    public static Map<String, List<ExcelRows>> readXlsForPath(String path, boolean iscontalTitle) throws IOException {
        InputStream is = new FileInputStream(path);
        return readXls(is,iscontalTitle);
    }

    public static <T extends ImportBase> List<T> readXlsForClazz(String path, boolean iscontalTitle, T clazz) throws Exception {
        InputStream is = new FileInputStream(path);
        return readXlsClazz(is,iscontalTitle,clazz);
    }

    /**
     * 读取Xls格式的excel到clazz中
     * @param is
     * @param iscontalTitle
     * @param clazz
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T extends ImportBase> List<T> readXlsClazz(InputStream is, boolean iscontalTitle, T clazz) throws Exception {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        // 存储excel数据
        List<T> sheetMap = new ArrayList<>();
        // Read the Sheet
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
        if (hssfSheet == null || hssfSheet.getLastRowNum() == 0) {
            return sheetMap;
        }
        //第一行的总列数
        HSSFRow firstxssfRow = hssfSheet.getRow(0);
        //获取总列数
        int columnNum = firstxssfRow.getPhysicalNumberOfCells();
        //checkColumn(columnNum,getColumnNum(clazz));
        // 获取类所有属性
        Field[] fields = clazz.getClass().getDeclaredFields();
        ImportExcelAnnotation importExcelAnnotation = null;
        int coumnIndex = 0;
        // Read the Row
        for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            if (iscontalTitle & rowNum == 0) {
                continue;
            }
            T obj = null;
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
            if (hssfRow != null) {
                // 创建实体
                if (obj == null) {
                    obj = (T) clazz.getClass().newInstance();
                }
                for (Field f : fields) {
                    // 设置属性可访问
                    f.setAccessible(true);
                    // 判断是否是注解
                    if (f.isAnnotationPresent(ImportExcelAnnotation.class)) {
                        // 获取注解
                        importExcelAnnotation = f.getAnnotation(ImportExcelAnnotation.class);
                        // 获取列索引
                        coumnIndex = importExcelAnnotation.columnIndex();
                        HSSFCell hSSFCell = hssfRow.getCell(coumnIndex);
                        //设置值
                        setCellValue(hSSFCell,f,obj, importExcelAnnotation);

                    }
                }
                // 添加到集合中,设置导入的行号
                ObjectCreateUtil.setFieldValue(obj,"importRowNum",rowNum+"");
                // 根据注解配置进行基础校验
                validateCell(obj);
                sheetMap.add(obj);
            }
        }
        return sheetMap;
    }

    /**
     * 设置对象的属性值
     * @param cell
     * @param f
     * @param obj
     * @param importExcelAnnotation
     * @param <T>
     * @throws Exception
     */
    private static  <T extends ImportBase> void setCellValue(Cell cell,Field f,T obj,ImportExcelAnnotation importExcelAnnotation)throws Exception{
        if(cell!=null){
            String value = getValueByCellType(cell, importExcelAnnotation.dateFormat());
            List<String> errorList = new ArrayList<>();
            if(value!=null){
                value=value.trim();
                //时间格式化
                String fieldType = f.getType().getSimpleName();
                if ("Date".equals(fieldType)) {
                    if(!StringUtils.isEmpty(value)){
                        String format=importExcelAnnotation.dateFormat();
                        if(StringUtils.isEmpty(importExcelAnnotation.dateFormat())){
                            format=DateUtils.DEFAULT_DATE_FORMAT;
                        }
                        try {
                            SimpleDateFormat simpleDateFormat=new SimpleDateFormat(format);
                            value=DateUtils.format(simpleDateFormat.parse(value),format);
                        }catch (Exception e){
                            errorList.add("时间格式应为【年-月-日】");
                        }
                    }
                }
                try {
                    ObjectCreateUtil.setFieldValue(obj,f.getName(),value);
                } catch (ApiException e) {
                    errorList.add("【"+importExcelAnnotation.columnName()+"】字段类型不为【"+f.getType().getSimpleName()+"】");
                }

            }
            obj.setValidateMessage(CollectionUtil.join(errorList, ";"));
        }
    }

    public static <T extends ImportBase> void validateCell(T object) {
        int index=0;
        //得到导入的行号
        Object row = object.getImportRowNum();
        if(row!=null){
            index=(int)row;
        }
        Field[] declaredFields = object.getClass().getDeclaredFields();
        List<String> errorList = new ArrayList<>();
        if(StrUtil.isNotBlank(object.getValidateMessage())){
            errorList.add(object.getValidateMessage());
        }
        for (Field field : declaredFields) {
            if (!field.isAnnotationPresent(ImportExcelAnnotation.class)){ continue; }
            ImportExcelAnnotation message = field.getAnnotation(ImportExcelAnnotation.class);
            //是否必须
            boolean columnRequire = message.columnRequire();
            //列名(导出时的标题)
            String columnName = message.columnName();
            //最大长度
            int maxLength = message.maxLength();
            //最小长度
            int minLength = message.minLength();
            //获取字段对应的值
            Object value = ObjectCreateUtil.getValueForField(object,field.getName());
            //验证必填字段
            if(columnRequire){
                if(StringUtils.isEmpty(value))  {
                    errorList.add("【"+columnName+"】字段为必填项,不能为空!");
                }
            }
            if(maxLength>0){
                if(value!=null&&value.toString().length()>maxLength){
                    errorList.add("【"+columnName+"】字段长度不能超过"+maxLength);
                }
            }
            if(minLength>0){
                if(value!=null&&value.toString().length()<minLength){
                    errorList.add("【"+columnName+"】字段长度不能小于"+minLength);
                }
            }

        }
        object.setValidateMessage(CollectionUtil.join(errorList, ";"));
    }

    /**
     * Read the Excel 2003-2007
     * path 路径
     * iscontalTitle 是否包含标题
     * @return
     * @throws IOException
     */
    public static Map<String, List<ExcelRows>> readXls(InputStream is, boolean iscontalTitle) throws IOException {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

        Map<String, List<ExcelRows>> sheetMap = new LinkedHashMap<String, List<ExcelRows>>();

        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            List<ExcelRows> list = new ArrayList<ExcelRows>();
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null || hssfSheet.getLastRowNum() == 0) {
                continue;
            }

            //第一行的总列数
            HSSFRow firstxssfRow = hssfSheet.getRow(0);
            //获取总列数
            int columnNum = firstxssfRow.getPhysicalNumberOfCells();
            // Read the Row
            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                if (iscontalTitle & rowNum == 0) {
                    continue;
                }

                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    ExcelRows excelRow = new ExcelRows();
                   /* HSSFCell firstxSSFCell = hssfRow.getCell(0);
                    if (firstxSSFCell == null) {
                        continue;
                    }*/
                    for (int i = 0; i < columnNum; i++) {
                        HSSFCell hSSFCell = hssfRow.getCell(i);
                        ExcelCols excelCols = new ExcelCols();
                        if(hSSFCell==null){
                            excelCols.setIndex(i);
                            excelCols.setValue(null);
                        }else {
                            hSSFCell.setCellType(CellType.STRING);
                            excelCols.setIndex(i);
                            excelCols.setValue(getValue(hSSFCell));
                            if (iscontalTitle) {
                                setCellTitleForHSSFCell(excelCols, hssfSheet.getRow(0).getCell(i));
                            }
                        }
                        excelRow.addColses(excelCols);
                    }
                    list.add(excelRow);

                }
            }
            sheetMap.put("sheet" + numSheet, list);
        }
        return sheetMap;
    }

    public static Map<String, List<ExcelRows>> readXlsxForPath(String path, boolean iscontalTitle) throws IOException {
        InputStream is = new FileInputStream(path);
        return readXlsx(is,iscontalTitle);
    }

    public static <T extends ImportBase> List<T> readXlsxForClazz(String path, boolean iscontalTitle, T clazz) throws Exception {
        InputStream is = new FileInputStream(path);
        return readXlsxClazz(is,iscontalTitle,clazz);
    }

    /**
     * 获取对象需要导入的个数
     * @param clazz
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> int getColumnNum( T clazz)throws Exception{
        int num=0;
        // 获取类所有属性
        Field[] fields = clazz.getClass().getDeclaredFields();
        for (Field f : fields) {
            // 设置属性可访问
            f.setAccessible(true);
            // 判断是否是注解
            if (f.isAnnotationPresent(ImportExcelAnnotation.class)) {
               num++;
            }
        }
        return num;
    }

    public static void checkColumn(int columnNum,int objectNum){
        if(columnNum!=objectNum){
            throw new ApiException("excel中导入的列数与对象中需要匹配的个数不一致!");
        }
    }



    public static <T extends ImportBase> List<T> readXlsxClazz(InputStream is, boolean iscontalTitle, T clazz) throws Exception {
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 存储excel数据
        List<T> sheetMap = new ArrayList<>();
        // Read the Sheet
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        if (xssfSheet == null || xssfSheet.getLastRowNum() == 0) {
            return sheetMap;
        }
        //第一行的总列数
        XSSFRow firstxssfRow = xssfSheet.getRow(0);

        // 获取类所有属性
        Field[] fields = clazz.getClass().getDeclaredFields();
        ImportExcelAnnotation importExcelAnnotation = null;
        int coumnIndex = 0;
        // Read the Row
        for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            if (iscontalTitle & rowNum <= clazz.getTitleIndex()) {
                continue;
            }
            T obj = null;
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            if (xssfRow != null) {
                // 创建实体
                if (obj == null) {
                    obj = (T) clazz.getClass().newInstance();
                }
                for (Field f : fields) {
                    // 设置属性可访问
                    f.setAccessible(true);
                    // 判断是否是注解
                    if (f.isAnnotationPresent(ImportExcelAnnotation.class)) {
                        // 获取注解
                        importExcelAnnotation = f.getAnnotation(ImportExcelAnnotation.class);
                        // 获取列索引
                        coumnIndex = importExcelAnnotation.columnIndex();
                        XSSFCell xSSFCell = xssfRow.getCell(coumnIndex);
                        //设置值
                        setCellValue(xSSFCell,f,obj,importExcelAnnotation);
                    }
                }
                // 添加到集合中，设置导入的行号
                ObjectCreateUtil.setFieldValue(obj,"importRowNum",rowNum+"");
                // 根据注解配置进行基础校验
                validateCell(obj);
                sheetMap.add(obj);
            }
        }
        return sheetMap;
    }
    /**
     * Read the Excel 2010
     *             path 路径
     *             iscontalTitle 是否包含标题
     * @return
     * @throws IOException
     */
    public static Map<String, List<ExcelRows>> readXlsx(InputStream is, boolean iscontalTitle) throws IOException {
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        Map<String, List<ExcelRows>> sheetMap = new LinkedHashMap<String, List<ExcelRows>>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            List<ExcelRows> list = new ArrayList<ExcelRows>();
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null || xssfSheet.getLastRowNum() == 0) {
                continue;
            }
            //第一行的总列数
            XSSFRow firstxssfRow = xssfSheet.getRow(0);
            //获取总列数
            int columnNum = firstxssfRow.getPhysicalNumberOfCells();

            // Read the Row
            for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                if (iscontalTitle & rowNum == 0) {
                    continue;
                }
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    ExcelRows excelRow = new ExcelRows();
                    /*XSSFCell firstxSSFCell = xssfRow.getCell(0);
                    if (firstxSSFCell == null) {
                        continue;
                    }*/
                    for (int i = 0; i < columnNum; i++) {
                        ExcelCols excelCols = new ExcelCols();
                        XSSFCell xSSFCell = xssfRow.getCell(i);
                        if(xSSFCell==null){
                            excelCols.setIndex(i);
                            excelCols.setValue(null);
                        }else {
                            xSSFCell.setCellType(CellType.STRING);
                            excelCols.setIndex(i);
                            excelCols.setValue(getValue(xSSFCell));
                            if (iscontalTitle) {
                                setCellTitleForXSSFCell(excelCols, xssfSheet.getRow(0).getCell(i));
                            }
                        }
                        excelRow.addColses(excelCols);
                    }

                    list.add(excelRow);
                }
            }
            sheetMap.put("sheet" + numSheet, list);
        }
        return sheetMap;
    }

    //获取每一列的标题
    public static ExcelCols setCellTitleForXSSFCell(ExcelCols excelCols, XSSFCell titleXSSFCell) {
        excelCols.setTitle(getValue(titleXSSFCell));
        return excelCols;
    }

    //获取每一列的标题
    public static ExcelCols setCellTitleForHSSFCell(ExcelCols excelCols, HSSFCell titleXSSFCell) {
        excelCols.setTitle(getValue(titleXSSFCell));
        return excelCols;
    }


    /**
     * NUMERIC            数值型        0
     *
     * STRING             字符串型      1
     *
     * FORMULA            公式型        2
     *
     * BLANK              空值         3
     *
     * BOOLEAN            布尔型       4
     *
     * ERROR              错误         5
     * @param cell
     * @return
     */
    @SuppressWarnings("static-access")
    private static String getValue(Cell cell) {
        String object=null;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                object = cell.getRichStringCellValue().getString();
                break;
            case BOOLEAN:
                object = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                object = "";
                break;
            default:
                object = cell.getStringCellValue();
                break;
        }
        return object;
    }

    private static String getValueByCellType(Cell cell, String dateFormat) {

        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case NUMERIC:
                String value;
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if(StringUtils.isEmpty(dateFormat)){
                        dateFormat = DateUtils.DEFAULT_DATE_FORMAT;
                    }
                    value = DateUtils.format(date,dateFormat);
                } else {
                    double dValue = cell.getNumericCellValue();
                    DecimalFormat df;
                    if(StringUtils.isEmpty(dateFormat) || Objects.equals("",dateFormat)) {
                        df = new DecimalFormat("0");
                    }   else {
                        df = new DecimalFormat(dateFormat);
                    }

                    value = df.format(dValue);
                }
                return value;
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default:
                return "";
        }
    }

    /**
     * get postfix of the path
     *
     * @param path
     * @return
     */
    public static String getPostfix(String path) {
        if (path == null || "".equals(path.trim())) {
            return "";
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return "";
    }

    /**
     * 获取对象的名称
     * @param clazz
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> String getClassName( Class<T> clazz)throws Exception{
        return clazz.getSimpleName();
    }

    /**
     * 导入:必输项和值长度检查
     *
     * @param entity 导入实体
     * @param declaredFields 导入实体的属性字段
     * @param lineNo 导入行号
     */
    public static <T extends Object> void baseCheckImportExcelAnnotation(T entity,Field[] declaredFields, int lineNo) {

        //序号
        Object serialNo = ObjectCreateUtil.getValueForField(entity,"serialNo");
        for (Field field : declaredFields) {
            if (!field.isAnnotationPresent(ImportExcelAnnotation.class)){ continue; }
            ImportExcelAnnotation importExcelAnnotation = field.getAnnotation(ImportExcelAnnotation.class);
            //是否必须
            boolean columnRequire = importExcelAnnotation.columnRequire();
            //列名(导出时的标题)
            String columnName = importExcelAnnotation.columnName();

            //验证必填字段
            Object value = ObjectCreateUtil.getValueForField(entity,field.getName());
            if(columnRequire){
                if(Objects.isNull(value)){
                    throw new ApiException(ExceptionMsgUtil.columnRequireErrorReason(lineNo,serialNo,columnName));
                }
            }
            //验证字段长度
            int maxLength = importExcelAnnotation.maxLength();
            if(maxLength > 0){
                if(Objects.nonNull(value) && value.toString().length() > maxLength){
                    throw new ApiException(ExceptionMsgUtil.maxLengthErrorReason(lineNo,serialNo,columnName,maxLength));
                }
            }
        }
    }

}
